create database Work
go
use Work
go

--二、综合题

--1. 描述（10分）
--针对如下表Author结构创建索引，写出创建索引的SQL语句：

CREATE TABLE Author 
(
   Id  int  NOT NULL PRIMARY KEY identity,
   FirstName  nvarchar(45) NOT NULL,
   LastName  nvarchar(45) NOT NULL,
   UpdatedTime  datetime NOT NULL
 )

--对FirstName创建唯一索引uniq_idx_firstname，对LastName创建普通索引idx_lastname


go

create unique nonclustered index uniq_idx_firstname on Author(FirstName desc)

go

create nonclustered index idx_lastname on Author(LastName asc)

go


--2. 描述（15分）
--构造一个触发器trg_AuditLog，在向Employees表中插入一条数据的时候，触发插入相关的数据到AuditLog中。

-- 职员表
CREATE TABLE Employees
(
   Id INT PRIMARY KEY NOT NULL identity,
   Name nvarchar(80) NOT NULL,
   Age INT NOT NULL,
   Address NVARCHAR(50),
   SALARY decimal(18,2)
);
-- 审计日志表
CREATE TABLE AuditLog
(
   Id int primary key not null identity,
   NAME TEXT NOT NULL,
   Salary decimal(18,2)
);

--后台会往Employees插入一条数据:
INSERT INTO Employees (NAME,AGE,ADDRESS,SALARY)VALUES ('Paul', 32, 'California', 20000.00 );
--然后从AuditLog里面使用查询语句:
select * from AuditLog;

--输出：
--Paul | 20000.00

go

create trigger trg_AuditLog
on Employees
for
instead of insert
as
begin
	insert into Employees
end

go

--3. 描述（10分）
--针对Author表创建视图vw_Author，只包含FirstName以及LastName两列，并对这两列重新命名，FirstName为v_FirstName，LastName修改为v_LastName：

CREATE TABLE Author 
(
   Id  int  NOT NULL PRIMARY KEY identity,
   FirstName  nvarchar(45) NOT NULL,
   LastName  nvarchar(45) NOT NULL,
   UpdatedTime  datetime NOT NULL
)
--后台会插入2条数据:
insert into Author (FirstName,LastName,UpdatedTime) values ('PENELOPE', 'GUINESS', '2006-02-15 12:34:33'), ('NICK', 'WAHLBERG', '2006-02-15 12:34:33');

--查询视图：select * from vw_Author;
--输出：
--['first_name_v', 'last_name_v']
--PENELOPE|GUINESS
--NICK|WAHLBERG


go

create view vw_Author as select FirstName as v_FirstName,LastName as v_LastName from Author

go

insert into vw_Author(v_FirstName,v_LastName)values(PENELOPE,GUINESS),(NICK,WAHLBERG)

go

select * from vw_Author


--4. 描述（10分）
--分页查询Employees表，每5行一页，返回第2页的数据


if(OBJECT_ID('Employees','U')is not null)
begin
	drop table Employees
end
CREATE TABLE Employees
(
   Id int primary key NOT NULL identity,
   EmployeeCode nvarchar(80),
   Birthday date NOT NULL,
   FirstName nvarchar(14) NOT NULL,
   LastName nvarchar(16) NOT NULL,
   Gender char(1) NOT NULL,
   HireDate date NOT NULL
);
--插入一些数据：
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10001','1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10002','1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10003','1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10004','1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10005','1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10006','1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10007','1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10008','1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10009','1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10010','1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10011','1953-11-07','Mary','Sluis','F','1990-01-22');

--查询（5行每页）第2页的记录，输出：
6|10006|1953-04-20|Anneke|Preusig|F|1989-06-02
7|10007|1957-05-23|Tzvetan|Zielinski|F|1989-02-10
8|10008|1958-02-19|Saniya|Kalloufi|M|1994-09-15
9|10009|1952-04-19|Sumant|Peac|F|1985-02-18
10|10010|1963-06-01|Duangkaew|Piveteau|F|1989-08-24


declare @i int=2 ,@j int=5
select * from Employees order by EmployeeCode asc offset (@i-1)*@j rows fetch next @j rows only


--5. 描述（30分）
--开发组收到M公司的开发邀约，拟开发一套OA办公自动化系统，该系统的部分功能及初步需求分析的结果如下 ：

--（1）M公司旗下有业务部、策划部和其他部门。部门信息包括部门号、部门名、主管、联系电话和邮箱号；每个部门只有一名主管，只负责管理本部门的工作，且主管参照员工关系的员工号；一个部门有多名员工，每名员工属于且仅属于一个部门。

--（2）员工信息包括员工号、姓名、职位、联系方式和薪资。职位包括主管、业务员、 策划员等。业务员负责受理用户申请，设置受理标志。一名业务员可以受理多个用户申请，但一个用户申请只能由一名业务员受理。

--（3）用户信息包括用户号、用户名、银行账号、电话、联系地址。用户号唯一标识用户信息中的每一个元组。

--（4）用户申请信息包括申请号、用户号、会议日期、天数、参会人数、地点、预算和受理标志（受理标志有：已申请（默认）、已拒绝、已受理、延期受理、已过期）。申请号唯一标识用户申请信息中的每一个元组，且一个用户可以提交多个申请，但一个用户申请只对应一个用户号。

--（5）策划部主管为己受理的用户申请制定会议策划任务。策划任务包括申请号、任务明细和要求完成时间.申请号唯一标识策划任务的每一个元组。一个策划任务只对应一个已受理的用户申请，但一个策划任务可由多名策划员参与执行，且一名策划员可以参与执行，且修改策划任务。

--題目1（10分）：使用PowerDesigner，设计可以使用的SqlServer数据库物理模型，要求中文名称和英文名称清楚（英文不知道的可以借助翻译软件），表关系准确；利用设计好的模型生成sql语句，创建一个名为OADatabase的数据库，备用；
create database OADatabase
go
use  OADatabase
go

/*==============================================================*/
/* Table: "Department information"                              */
/*==============================================================*/
create table "Departmentinformation" 
(
   department           int                            not null,
   departmentName       nvarchar(80)                   null,
   departmentmanager nvarchar(80)                   null,
   contactnumber     int                            null,
   email                nvarchar(80)                   null,
   constraint "PK_DEPARTMENT INFORMATION" primary key clustered (department),
   constraint AK_UNIQUE_DEPARTMENTM_DEPARTME unique (departmentmanager)
);

/*==============================================================*/
/* Table: "User application information table"                  */
/*==============================================================*/
create table "Userapplicationinformationtable" 
(
   applicationnumber                  int                            not null,
   userid                  int                            null,
   dateofconference       datetime                       null,
   workday                   int                            null,
   usercount                 int                            null,
   place                   nvarchar(80)                   null,
   usermoney                   money                          null,
   Toacceptthelogo                 nvarchar(80)                   null,
   constraint "PK_USER APPLICATION INFORMATIO" primary key clustered (applicationnumber)
);

/*==============================================================*/
/* Table: "employee information table"                          */
/*==============================================================*/
create table "employeeinformationtable" 
(
   jobnumber         int                            not null,
   jobname           nvarchar(80)                   null,
   job                  nvarchar(80)                   null,
   phone                int                            null,
   workmoney                money                          null,
   constraint "PK_EMPLOYEE INFORMATION TABLE" primary key clustered (jobnumber)
);
--题目2（10分）：为了保证当用户申请会议，受理标志为 已申请，请设计一个触发器，当用户申请信息表插入数据的时候，
--可以判断其默认受理标志是否为：已申请，如果是，则无需处理允许正常插入，如果不是则重新个性受理标志为：已申请；

insert into "employeeinformationtable"(job)values('主管'),('业务员'),('策划员')

go

create trigger tr_user_jobnumber
on employeeinformationtable
for
instead of insert
as
begin
	INSERT INTO Userapplicationinformationtable
end

--题目3（10分）：当业务员受理用户申请的时候，需要将当前处理的记录中受理标志修改为：已受理，同时将其它所有状态为：
--已申请，且会议日期已超过的记录中的受理标志个性为：已过期，请设计一个存储过程来处理此业务；

go

create proc proc_business
@Toacceptthelogo nvarchar(80),
@state nvarchar(80),
@dateofaconference datetime
as
begin
	if ()
	begin
		set @Toacceptthelogo='已受理'
		set @state='已申请'
		set @dateofaconference='已过期'
	end
end

go